﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Purchases
{
    public class PurchaseOrderSVCManager:ConnectionManager
    {
        #region Others
        public bool IsDocumentExists(string _DOCUMENT_NUM)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPORSVC WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DOCUMENT_NUM));
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _ADAPTER.SelectCommand = oCommand;
            _ADAPTER.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region Add/Update Order
        public void AddPurchaseOrder(PurchaseOrderSVCUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OPORSVC (DocNum, DocStatus, PostingDate, DueDate, DocDate, CardCode, CardName, ContactPerson, Reference, BillTo, ShipTo, Remarks, RequestedBy, Division, Department, EntityCode, WithHoldingTax, VATCode, VATRate, IsVATInclusive, GrossAmount, VATAmount, NetAmount) VALUES " +
                                                        " (@DocNum, @DocStatus, @PostingDate, @DueDate, @DocDate, @CardCode, @CardName, @ContactPerson, @Reference, @BillTo, @ShipTo, @Remarks, @RequestedBy, @Division, @Department, @EntityCode, @WithHoldingTax, @VATCode, @VATRate, @IsVATInclusive, @GrossAmount, @VATAmount, @NetAmount)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@EntityCode", oUnit.EntityCode));
            
            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));

            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));

            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATCode", oUnit.TaxCode));
            oCommand.Parameters.Add(new SqlParameter("@VATRate", oUnit.TaxRate));
            oCommand.Parameters.Add(new SqlParameter("@IsVATInclusive", oUnit.IsVATInclusive));

            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));



            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO PORSVC1 (DocNum, Particulars, AcctCode, AcctName, Amount, ItemGuid) VALUES " +
                                                                "(@DocNum, @Particulars, @AcctCode, @AcctName, @Amount, @ItemGuid)";



                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"].ToString())));
                oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));
                
                oCommand.ExecuteNonQuery();

            }
        }

        public void UpdatePurchaseOrder(PurchaseOrderSVCUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPORSVC SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, Reference=@Reference, BillTo=@BillTo, ShipTo=@ShipTo, Remarks=@Remarks, RequestedBy=@RequestedBy, Division=@Division, Department=@Department, EntityCode=@EntityCode, GrossAmount=@GrossAmount, VATAmount=@VATAmount, NetAmount=@NetAmount, WithHoldingTax=@WithHoldingTax" +
                                                        " WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
            oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

            oCommand.Parameters.Add(new SqlParameter("@EntityCode", oUnit.EntityCode));

            oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
            oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
            oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
            oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));

            oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
            oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
            oCommand.Parameters.Add(new SqlParameter("@RequestedBy", oUnit.RequestedBy));
            oCommand.Parameters.Add(new SqlParameter("@Department", oUnit.Department));
            oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));

            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));

            oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
            oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
            oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE PORSVC1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {

                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO PORSVC1 (DocNum, Particulars, AcctCode, AcctName, Amount, ItemGuid) VALUES " +
                                                            "(@DocNum, @Particulars, @AcctCode, @AcctName, @Amount, @ItemGuid)";



                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Amount", Convert.ToDouble(oRow["Amount"].ToString())));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"].ToString()));

                    oCommand.ExecuteNonQuery();


                }

            }
        }
        #endregion

        #region Post/Cancel/Close Order
        public void PostPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPORSVC SET DOCSTATUS='Posted' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void CancelPO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPORSVC SET DOCSTATUS='Canceled' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void ChangePrintStatus(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPORSVC SET DOCSTATUS='Printed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }

        public void ClosePO(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OPORSVC SET DOCSTATUS='Closed' WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocumentNumber));
            oCommand.ExecuteNonQuery();
        }
        #endregion

        #region Getting Data

        public DataTable GetPostedPO(string _REFERENCE_PO)
        {
            SqlCommand oCommand = new SqlCommand();
            _REFERENCE_PO = '%' + _REFERENCE_PO.Trim() + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPORSVC where DOCSTATUS='Posted' AND DOCNUM LIKE @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _REFERENCE_PO));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedPO()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPORSVC where DOCSTATUS='Posted'";

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPostedPOByDocNum(string _DocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPORSVC WHERE DOCNUM = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DOCNUM", _DocumentNumber));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseOrderBySupplier(string _CardName)
        {
            _CardName = '%' + _CardName.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPORSVC WHERE CardName LIKE @CardName ORDER BY POSTINGDATE DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _CardName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseOrders(string sDocumentNumber)
        {
            sDocumentNumber = '%' + sDocumentNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, CardCode, CardName, DocStatus FROM OPORSVC WHERE DocNum LIKE @DOCNUM ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetPurchaseOrder(string _CardCode, string _DocNumber, string _DocumentStatus)
        {
            _DocNumber = '%' + _DocNumber.Trim() + '%';
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT TOP 500 DocNum, PostingDate, DueDate, CardCode, CardName, NetAmount, DocStatus FROM OPORSVC WHERE CardCode=@CardCode AND DocNum LIKE @DOCNUM AND DocStatus=@DocStatus ORDER BY PostingDate DESC";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _CardCode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public string GetPurchaseOrderNumber(string _Cardcode, string _DocNumber, string _DocumentStatus)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT DocNum FROM OPORSVC WHERE CardCode=@CardCode AND DocNum=@DOCNUM AND DocStatus=@DocStatus";
            oCommand.Parameters.Add(new SqlParameter("@CardCode", _Cardcode));
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", _DocumentStatus));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count == 0)
            {
                return "";
            }
            else
            {
                DataRow oRow = ds.Tables[0].Rows[0];
                return oRow["DocNum"].ToString();
            }

        }

        public DataRow GetPurchaseOrder(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OPORSVC WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            DataRow oRow = ds.Tables[0].Rows[0];

            return oRow;
        }

        public DataTable GetPOLineItems(string sDocumentNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT *, '' LotNumber, CAST(NULL AS DateTime) ExpiryDate FROM PORSVC1 WHERE DocNum = @DOCNUM";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", sDocumentNumber));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);



            return ds.Tables[0];
        }

        #endregion

        #region Report Retreivable

        public DataTable GetPOHeaderServicesByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_POSVC";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetPOHeadersByPostingDate(DateTime _FROM, DateTime _TO, bool _Draft, bool _Posted, bool _Open, bool _Closed)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LOGS_PO";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            oCommand.Parameters.Add(new SqlParameter("@WITHDRAFT", Convert.ToInt16(_Draft)));
            oCommand.Parameters.Add(new SqlParameter("@WITHPOSTED", Convert.ToInt16(_Posted)));
            oCommand.Parameters.Add(new SqlParameter("@WITHOPEN", Convert.ToInt16(_Open)));
            oCommand.Parameters.Add(new SqlParameter("@WITHCLOSED", Convert.ToInt16(_Closed)));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetPurchaseOrderStatus(DateTime _FROM, DateTime _TO)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_PO_SERVICE_STATUS";
            oCommand.Parameters.Add(new SqlParameter("@FROM", _FROM));
            oCommand.Parameters.Add(new SqlParameter("@TO", _TO));

            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetLastPurchasePrice(string sItemCode)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.StoredProcedure;
            oCommand.CommandText = "SP_LAST_PUR_PRICE";
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", sItemCode));


            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        #endregion
    }
}
